package com.boot.core.task.report122.conn

import com.boot.core.task.report122.dto.TaAccidentReport
import org.springframework.stereotype.Component
import java.sql.*
import java.text.SimpleDateFormat
import java.time.LocalDateTime
import java.time.format.DateTimeFormatter
import java.time.temporal.TemporalAdjusters
import java.util.*
import java.util.Date


@Component
class OracleDBConn {

    /**
     * 获取122 oracle数据库 dsdb.V_JJD 视图中的数据
     * @return
     */
    fun oracleSyncReportData(): List<Any> {
        var rs: ResultSet? = null
        var stmt: Statement? = null
        var ps: PreparedStatement? = null
        var conn: Connection? = null
        val oracList: ArrayList<Any> = ArrayList<Any>()
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver")

            val dbURL122 = "jdbc:oracle:thin:@10.173.11.206:1521:dsdb"
            conn = DriverManager.getConnection(dbURL122, "ds_yhst", "ds_yhst")

            val lastBJSJ: String = LocalDateTime.now().with(TemporalAdjusters.firstDayOfMonth())
                .format(DateTimeFormatter.ofPattern("yyyy-MM-dd"))
            val lastGXSJ: String = LocalDateTime.now().with(TemporalAdjusters.lastDayOfMonth()).plusDays(1)
                .format(DateTimeFormatter.ofPattern("yyyy-MM-dd"))

            val sqlTemplate =
                "SELECT * from V_JJD where (SCBJSJ >  to_date('$lastBJSJ', 'yyyy-mm-dd hh24:mi:ss') or GXSJ > to_date('$lastGXSJ', 'yyyy-mm-dd hh24:mi:ss')) AND (XQDW = '610100000000' OR XZQH = '610100')"
            ps = conn.prepareStatement(sqlTemplate)
            rs = ps.executeQuery()
            while (rs.next()) {
                val accident = TaAccidentReport()
                accident.setAccept_personnel(rs.getString(42))
                accident.setAccept_party(rs.getString(7))
                //*********测试添加
                accident.setAccept_time(rs.getString(12))
                accident.setAccident_id(-1)
                accident.setDescription(rs.getString(27))
                accident.setType(1)
                accident.setAddress(rs.getString(8))
                accident.setContact_name(rs.getString(13))
                accident.setContact_cell_number(rs.getString(14))
                accident.setIs_accident_personnel(rs.getInt(58))
                accident.setContent(rs.getString(29))
                if (rs.getString(50) == null) {
                    accident.setPlate_number_list("无")
                } else {
                    accident.setPlate_number_list(rs.getString(50))
                }
                val day = Date()
                val df = SimpleDateFormat("yyyy-MM-dd HH:mm:ss")
                accident.setAdd_time(df.format(day))
                accident.setJjdbh(rs.getString("jjdbh"))
                if (rs.getString(65) == null) {
                    accident.setCjsj("")
                } else {
                    accident.setCjsj(rs.getString(65))
                }
                if (rs.getString(66) == null) {
                    accident.setGxsj(null)
                } else {
                    accident.setGxsj(rs.getString(66))
                }
                accident.setSldbh(rs.getString("sldbh"))
                accident.setXqdw(rs.getString("xzqh")) //6位
                accident.setXzqh(rs.getString("xqdw")) //12位
                oracList.add(accident)
            }
            println("connect success ==> Oracle Connection 1 ===> Sync Data Successful")
        } catch (e: ClassNotFoundException) {
            e.printStackTrace()
        } catch (e: SQLException) {
            e.printStackTrace()
        } finally {
            try {
                rs?.close()
                stmt?.close()
                ps?.close()
                conn?.close()
            } catch (e: SQLException) {
                e.printStackTrace()
            }
        }
        return oracList
    }

    fun getTimes(): Map<*, *>? {
        var rs: ResultSet? = null
        var stmt: Statement? = null
        var ps: PreparedStatement? = null
        var conn: Connection? = null
        val resultMap: MutableMap<String, String> = HashMap()
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver")

            val dbURL122 = "jdbc:oracle:thin:@10.173.11.206:1521:dsdb"
            conn = DriverManager.getConnection(dbURL122, "ds_yhst", "ds_yhst")
            val sqlGetlastSCBJSJ = "SELECT max(gxsj) as gxsj,max(accept_time) as accept_time from ta_accident_report"
            ps = conn.prepareStatement(sqlGetlastSCBJSJ)
            rs = ps.executeQuery()
            while (rs.next()) {
                val lastGXSJ = rs.getString("gxsj") //更新时间
                val lastBJSJ = rs.getString("accept_time") //报警时间
                resultMap["lastGXSJ"] = lastGXSJ
                resultMap["lastBJSJ"] = lastBJSJ
            }
        } catch (e: ClassNotFoundException) {
            e.printStackTrace()
        } catch (e: SQLException) {
            e.printStackTrace()
        } finally {
            try {
                rs?.close()
                stmt?.close()
                ps?.close()
                conn?.close()
            } catch (e: SQLException) {
                e.printStackTrace()
            }
        }
        return resultMap
    }

    fun oracleDB122ConnTest2() {
        var rs: ResultSet? = null
        var stmt: Statement? = null
        var ps: PreparedStatement? = null
        var conn: Connection? = null
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver")

            val dbURL122 = "jdbc:oracle:thin:@//10.173.11.206:1521/dsdb"
            conn = DriverManager.getConnection(dbURL122, "ds_yhst", "ds_yhst")

            println("connect success ==> Oracle Connection 2")
            println("Oracle JDBC Thin using a ServiceName connect success")
        } catch (e: ClassNotFoundException) {
            e.printStackTrace()
        } catch (e: SQLException) {
            e.printStackTrace()
        } finally {
            try {
                rs?.close()
                stmt?.close()
                ps?.close()
                conn?.close()
            } catch (e: SQLException) {
                e.printStackTrace()
            }
        }
    }

    fun oracleDB122ConnTest3() {
        var rs: ResultSet? = null
        var stmt: Statement? = null
        var ps: PreparedStatement? = null
        var conn: Connection? = null
        try {
            Class.forName("oracle.jdbc.driver.OracleDriver")

//            var dbURL: String = ("jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.203)(PORT=15211)))"
//                    + "(CONNECT_DATA=(SERVICE_NAME=orcl)))")
//            conn = DriverManager.getConnection(dbURL, "TRFF_APP", "trffapp123")

            val dbURL122 =
                ("jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.173.11.206)(PORT=1521)))"
                        + "(CONNECT_DATA=(SERVICE_NAME=dsdb)))")
            conn = DriverManager.getConnection(dbURL122, "ds_yhst", "ds_yhst")

            println("connect success ==> Oracle Connection 3")
            println("Oracle JDBC Thin using a TNSName connect success")
        } catch (e: ClassNotFoundException) {
            e.printStackTrace()
        } catch (e: SQLException) {
            e.printStackTrace()
        } finally {
            try {
                rs?.close()
                stmt?.close()
                ps?.close()
                conn?.close()
            } catch (e: SQLException) {
                e.printStackTrace()
            }
        }
    }

//    companion object {
//        @JvmStatic
//        fun main(args: Array<String>) {
//            val rs = "2021-02-07 12:58:59"
//            val day1 = Date()
//            val df1 = SimpleDateFormat("yyyy-MM-dd")
//            println(df1.format(day1) + rs.substring(10, 16))
//        }
//    }

}